package com.lrm.dao;

import com.lrm.po.Blog;
import com.lrm.po.User;
import com.lrm.util.DBUtils;
import org.springframework.data.jpa.repository.Query;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class BlogController {
    public static Blog findOne(Long id){
        Blog blog = null;
        try {
            Connection con = DBUtils.getConnection();
            String sql = "select * from t_blog where id = ?";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setObject(1, id);
            ResultSet result = ps.executeQuery();
            int i = 0;
            while (result.next()) {
                if (i < 1) {
                    blog = new Blog();
                    blog.setId(result.getLong("id"));
                    blog.setAppreciation(result.getBoolean("appreciation"));
                    blog.setCommentabled(result.getBoolean("commentabled"));
                    blog.setContent(result.getString("content"));
                    blog.setCreateTime(result.getDate("create_time"));
                    blog.setDescription(result.getString("description"));
                    blog.setFirstPicture(result.getString("first_picture"));
                    blog.setFlag(result.getString("flag"));
                    blog.setPublished(result.getBoolean("published"));
                    blog.setRecommend(result.getBoolean("recommend"));
                    blog.setShareStatement(result.getBoolean("share_statement"));
                    blog.setTitle(result.getString("title"));
                    blog.setUpdateTime(result.getDate("update_time"));
                    blog.setViews(result.getInt("views"));
                    blog.setType(TypeController.findOne(result.getLong("type_id")));
                    blog.setUser(UserController.findOne(result.getLong("user_id")));
                } else {
                    break;
                }
            }
        }
        catch (Exception e){
            throw new RuntimeException(e);
        }
        return blog;
    }

    public static int updateViews(Long id){
        int rows = 0;
        try {
            Connection con = DBUtils.getConnection();
            String sql = "CALL update_blog_views(" + id + ");";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.execute(sql);
        }
        catch (Exception e){
            throw new RuntimeException(e);
        }
        return rows;
    }

    public static List<String> findGroupYear() {
        ArrayList<String> allYears = new ArrayList<>();
        try {
            Connection con = DBUtils.getConnection();
            String sql = "SELECT * FROM all_year_views";
            PreparedStatement ps = con.prepareStatement(sql);
            ResultSet result = ps.executeQuery();
            while (result.next()) {
                allYears.add(result.getString("update_year"));
            }
        }
        catch (Exception e){
            throw new RuntimeException(e);
        }
        return allYears;
    }

    public static List<Blog> findByYear(String year) {
        ArrayList<Blog> allBlogs = new ArrayList<>();
        try {
            Connection con = DBUtils.getConnection();
            String sql = "SELECT * FROM year_blog_views where update_blog_year = ?";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setObject(1, year);
            ResultSet result = ps.executeQuery();
            while (result.next()) {
                Blog blog = new Blog();
                blog.setId(result.getLong("id"));
                blog.setAppreciation(result.getBoolean("appreciation"));
                blog.setCommentabled(result.getBoolean("commentabled"));
                blog.setContent(result.getString("content"));
                blog.setCreateTime(result.getDate("create_time"));
                blog.setDescription(result.getString("description"));
                blog.setFirstPicture(result.getString("first_picture"));
                blog.setFlag(result.getString("flag"));
                blog.setPublished(result.getBoolean("published"));
                blog.setRecommend(result.getBoolean("recommend"));
                blog.setShareStatement(result.getBoolean("share_statement"));
                blog.setTitle(result.getString("title"));
                blog.setUpdateTime(result.getDate("update_time"));
                blog.setViews(result.getInt("views"));
                blog.setType(TypeController.findOne(result.getLong("type_id")));
                blog.setUser(UserController.findOne(result.getLong("user_id")));
                allBlogs.add(blog);
            }
        }
        catch (Exception e){
            throw new RuntimeException(e);
        }
        return allBlogs;
    }
}
